import { SchemaInspector } from 'knex-schema-inspector'
import 'dayjs/locale/zh-cn'

const inspector = SchemaInspector(knex)

const router = createRouter()

router.post('/standard/:table', eventHandler(async (event) => {
  const params: any = event.context.params
  const reqTable = params.table
  let table = reqTable
  let schema
  if (reqTable.split('.').length > 1) {
    schema = reqTable.split('.')[0]
    table = reqTable.split('.')[1]
  }

  let knexSchema = knex.schema
  if (schema && inspector.withSchema) {
    knexSchema.withSchema(schema)
    try {
      // 判断 schema 是否存在
      inspector.withSchema(schema)
      await inspector.tableInfo(table)
    } catch (e) {
      event.node.res.statusCode = 301
      return { code: 301, msg: `模式(schema) '${schema}' 不存在` }
    }
  }
  const hasTable = knexSchema.hasTable(table)
  if (!hasTable) {
    return { code: 301, msg: `表 '${reqTable}' 不存在` }
  }
  const tableColumns = await inspector.columnInfo(table)

  const field: any = []
  for (let i = 0; i < STANDARD_FIELD.length; i++) {
    const idx = tableColumns.findIndex(e => e.name === STANDARD_FIELD[i].columnName)
    if (idx > -1) { continue }
    field.push(STANDARD_FIELD[i])
  }
  await knexSchema.alterTable(table, function (t) {
    field.forEach(e => {
      if (e.columnType === 'timestamp') {
        if (!isNil(e.default)) {
          t.timestamp(e.columnName, { precision: 6, useTz: false }).defaultTo(knex.fn.now(6)).comment(e.comment)
        } else {
          t.timestamp(e.columnName, { precision: 6, useTz: false }).comment(e.comment)
        }
      } else if (e.columnType === 'bigint') {
        t[e.columnType](e.columnName).unsigned().comment(e.comment)
      } else if (!isNil(e.default)) {
        t[e.columnType](e.columnName).defaultTo(e.default).comment(e.comment)
      } else {
        t[e.columnType](e.columnName).comment(e.comment)
      }
    })
  })

  return { code: 0, msg: 'success', result: field }
}))

router.post('/createTable/:table', eventHandler(async (event) => {
  const body = await readBody(event)
  const params: any = event.context.params
  const reqTable = params.table
  if (isNilOrEmpty(body) || isNilOrEmpty(body.columns)) {
    event.node.res.statusCode = 301
    return { code: 301, msg: '参数错误' }
  }

  const AUTO_ADD_FIELD = ['id', 'created_at', 'updated_at', 'deleted_at', 'created_by', 'updated_by', 'deleted_by', 'is_deleted', 'remark', 'version']
  const columns = body.columns.filter(e => !AUTO_ADD_FIELD.includes(e.colName))
  const table_comment = body.comment

  let table = reqTable
  let schema
  if (reqTable.split('.').length > 1) {
    schema = reqTable.split('.')[0]
    table = reqTable.split('.')[1]
  }

  if (isNilOrEmpty(columns)) {
    event.node.res.statusCode = 301
    return { code: 301, msg: '请填写字段！' }
  } else if (type(columns) !== 'Array') {
    event.node.res.statusCode = 301
    return { code: 301, msg: '字段格式错误！示例见 result。', result: { 'columns': [{ 'colName': 'test', 'colType': 'string', 'comment': '测试' }] } }
  }

  let notValidItem
  columns.some(e => {
    const item: any = knexType.find(v => v.typname === e.colType || v.alias === e.colType)
    if (isNilOrEmpty(item)) {
      notValidItem = e
      return true
    }
    if (item.alias) { e.colType = item.typname }
    return false
  })
  if (isNotNilOrEmpty(notValidItem)) {
    event.node.res.statusCode = 301
    return { code: 301, msg: '字段类型错误！详情见 result。', result: { '错误字段': notValidItem, 'colType可用类型': 'string, smallint, bigint, integer, decimal, boolean, timestamp, text, jsonb' } }
  }

  let knexSchema = knex.schema
  if (schema && inspector.withSchema) {
    knexSchema.withSchema(schema)
    try {
      // 判断 schema 是否存在
      inspector.withSchema(schema)
      await inspector.tableInfo(table)
    } catch (e) {
      event.node.res.statusCode = 301
      return { code: 301, msg: `模式(schema) '${schema}' 不存在` }
    }
  }

  const exists = await knexSchema.hasTable(table)
  if (exists) {
    const countResp = await knex.table(table).count('id', { as: 'count' })
    const count = countResp[0].count
    if (Number(count) > 0) {
      event.node.res.statusCode = 302
      return { code: 302, result: count, msg: `数据表已存在，有 ${count} 条数据！` }
    }
    await knexSchema.dropTableIfExists(table)
  }

  await knexSchema.createTable(table, function (t) {
    // default column
    t.bigIncrements('id').primary()
    // if (['pg', 'postgres', 'postgresql'].includes(knex.client.driverName)) {
    //   t.string("uid").defaultTo(knex.raw('uuid_generate_v4()'))
    // } else if (knex.client.driverName === 'mysql' && knex.client.version > 6) {
    //   t.string("uid").defaultTo(knex.raw('(UUID())'))
    // }

    columns.filter(e => !RESERVED_FIELD.includes(e.colName) && e.colName !== 'id').forEach(e => {
      let col
      switch (e.colType) {
        case 'decimal':
          col = t.decimal(e.colName, 10, 2)
          break
        case 'timestamp':
          col = t.timestamp(e.colName, { precision: 6, useTz: false }).nullable()
          break
        case 'string':
          if (type(e.colLen) === 'Number' && e.colLen > 0) {
            col = t.string(e.colName, e.colLen)
          } else {
            col = t.string(e.colName)
          }
          break
        default:
          col = t[e.colType](e.colName)
          break
      }
      if (isNotNilOrEmpty(e.unique)) {
        col.unique()
      }
      if (isNotNilOrEmpty(e.comment)) {
        col.comment(e.comment)
      }
    })

    t.timestamp('created_at', { precision: 6, useTz: false }).defaultTo(knex.fn.now(6)).comment('创建时间')
    t.timestamp('updated_at', { precision: 6, useTz: false }).nullable().comment('更新时间')
    t.timestamp('deleted_at', { precision: 6, useTz: false }).nullable().comment('删除时间')
    t.bigint('created_by').unsigned().comment('创建人')
    t.bigint('updated_by').unsigned().comment('更新人')
    t.bigint('deleted_by').unsigned().comment('删除人')
    t.tinyint('is_deleted').defaultTo(0).comment('是否删除')
    t.string('remark').comment('备注')
    t.integer('version', 8).defaultTo(0).comment('乐观锁版本号')
    if (isNotNilOrEmpty(table_comment)) {
      t.comment(table_comment)
    }
  })

  return { code: 0, msg: 'success' }
}))

router.get('/getTables', eventHandler(async (event) => {
  const query = getQuery(event)
  const tableSchema = query.schema?.toString() || ''
  try {
    if (isNotNilOrEmpty(tableSchema) && inspector.withSchema) {
      inspector.withSchema(tableSchema)
    }
    const tables = await inspector.tables()
    const res = tables.map(async item => {
      return await inspector.tableInfo(item)
    })
    const result = await Promise.all(res)
    return { code: 0, msg: 'success', result }
  } catch (e) {
    return { code: 201, msg: '获取数据表信息失败！', result: e?.toString() }
  }
}))

router.get('/getTablesWithColumn', eventHandler(async (event) => {
  const query = getQuery(event)
  const tableSchema = query.schema?.toString() || ''
  try {
    if (isNotNilOrEmpty(tableSchema) && inspector.withSchema) {
      inspector.withSchema(tableSchema)
    }
    const tables = await inspector.tables()
    const tableRes = tables.map(async item => {
      return await inspector.tableInfo(item)
    })
    const colRes = tables.map(async item => {
      return await inspector.columnInfo(item)
    })
    const resp = await Promise.all([...tableRes, ...colRes])
    const respTables: any = []
    const respCols = {}
    resp.forEach(e => {
      if (isArray(e)) {
        respCols[e[0].table] = e
      } else {
        respTables.push(e)
      }
    })
    const result = respTables.map(e => {
      e.columns = respCols[e.name]
      return e
    })
    return { code: 0, msg: 'success', result }
  } catch (e) {
    return { code: 201, msg: '获取数据表信息失败！', result: e?.toString() }
  }
}))

// pg: /getTableInfo/schema.table ; mysql: /getTableInfo/table
router.get('/getTableInfo/:table', eventHandler(async (event) => {
  const req: any = event.context
  const reqTable = req.params.table

  let table = reqTable
  let schema
  if (reqTable.split('.').length > 1) {
    schema = reqTable.split('.')[0]
    table = reqTable.split('.')[1]
  }

  if (isNotNilOrEmpty(schema) && inspector.withSchema) {
    try {
      inspector.withSchema(schema)
      const result = await inspector.tableInfo(table)
      return { code: 0, msg: 'success', result }
    } catch (e) {
      // console.log(e)
      return { code: 0, msg: 'table 不存在', result: e?.toString() }
    }
  }
  const result = await inspector.tableInfo(table)
  return { code: 0, msg: 'success', result }
}))

// pg: /getColumns/schema.table ; mysql: /getColumns/table
router.get('/getColumns/:table', eventHandler(async (event) => {
  const req: any = event.context
  const reqTable = req.params.table

  let table = reqTable
  let schema
  if (reqTable.split('.').length > 1) {
    schema = reqTable.split('.')[0]
    table = reqTable.split('.')[1]
  }
  try {
    if (isNotNilOrEmpty(schema) && inspector.withSchema) {
      inspector.withSchema(schema)
      const result = await inspector.columnInfo(table)
      return { code: 0, msg: 'success', result }
    }
    const result = await inspector.columnInfo(table)
    return { code: 0, msg: 'success', result }
  } catch (e) {
    event.node.res.statusCode = 201
    return { code: 201, msg: '获取表字段失败', result: e?.toString() }
  }
}))

// 接口管理：查询接口
router.post('/getDictApi', eventHandler(async (event) => {
  const reqBody = await readBody(event)
  try {
    const resp = await knex.select('*').from('dict_api').where('api_method', reqBody.api_method).where('api_path', reqBody.api_path)
    return { code: 0, msg: 'success', result: { dataList: resp } }
  } catch (e) {
    console.log(e)
    return { code: 201, msg: '接口查询失败!', result: e?.toString() }
  }
}))
// 接口管理：添加接口
router.put('/saveDictApi', eventHandler(async (event) => {
  const formData = await readBody(event)
  const headers = getHeaders(event)
  if (isNilOrEmpty(headers['user-id'])) {
    setResponseStatus(event, 401)
    return { code: 401, msg: '请登录!' }
  }
  // 保留字段清理
  try {
    const result = await upsertRows('dict_api', [formData], headers['user-id'])
    if (result.code !== 0) {
      event.node.res.statusCode = result.code
    }
    return result
  } catch (e) {
    console.log(e)
    return { code: 201, msg: '接口保存失败!', result: e?.toString() }
  }
}))
// 接口管理：删除接口
router.delete('/delDictApi/:id', eventHandler(async (event) => {
  const headers = getHeaders(event)
  if (isNilOrEmpty(headers['user-id'])) {
    setResponseStatus(event, 401)
    return { code: 401, msg: '请登录!' }
  }
  const params: any = event.context.params
  try {
    const result = await deleteRowHard('dict_api', params.id)
    if (result) {
      event.node.res.statusCode = 500
      return { code: 201, msg: '接口删除失败!', result }
    }
    return { code: 0, msg: '接口已删除!' }
  } catch (e) {
    return { code: 201, msg: '接口删除失败!', result: e?.toString() }
  }
}))

export default useBase('/api/db', router.handler)
